Integration des prix du carburants quotidien dans un datawarehouse



Compétences exploitées

SQL, HeidiSQL, MariaDB, Apache Hop, ETL, Datawarehouse

Données et contexte

Contexte: On veut initialiser un entrepôt de données contenant pour chaque date les prix de chaque carburant dans chaque station essence de France, on souhaite pouvoir en un clique obtenir le tableaux qui donnera les données souhaitées.
Données : on dispose d'un fichier de l'Insee concernant les prix du carburants pour chaque station service, cependant , la manière dont les données sont rangées dans le fichier XML ne correspondent pas à la table qu'on souhaite obtenir ce qui obligera plusieurs modifications(extrait sur le côté).

Création des tables

Avant d'aller sur Apache Hop pour configurer les pipelines , on créé une base sur Heidi SQL qu'on connectera par la suite à Apache Hop, la base est hébergé sur MariaDB . On créé les trois tables demandées: nos deux tables de dimensions d_date et d_station ansi que notre table de faits f_carburants.On rajoute DROP TABLE au début ce qui efface la table et permettra de pouvoir tester plusieurs fois nos pipelines quand elles seront prêtes.

Liaison avec la base de données

Après ça on créé un environnement de developpement dans Apache Hop et on fait correspondre les variables avec les valeurs afin de pouvoir lié la base HeidiSQL. On utilise ensuite la fonction metadata Relational Database d'Apache HOP pour relier la base de données à Apache HOP ce qui permettra d'alimenter les tables en données.

Alimentation en données des tables

Table date

On commence par générer le calendrier qui servira à alimenter la table date. Pour cette étape, on n'a pas besoin de fichier externe, les fonctions du logiciel suffisent. On initie la date au premier janvier 2020 et on incrémente d'un jour 10 000 fois pour être sûr d'être jusqu'à aujourd'hui. L'étape la plus importante ici, on utilise la caclulatrice afin d'extraire chaque élément de la date, à partir de la date complète on obtien automatiquement le numéro de semaine, le jour, le numéro de l'année etc...
Vous avez la pipeline annotée sur le côté, si vous avez du mal à la voir n'hésitez pas à télécharger le livrable.

Table station

On passe au remplissage de la table station. Ici , le plus dur a été d'extraire les codes des départements et des régions à partir des fichiers XML de l'Insee. Cette étape là peut être plus délicate que la précédente car on a eu besoin d'importer les données, si on ne fait pas attention au rangement des dossiers on peut vite ne peut pas pouvoir extraire ou avoir une erreur sans savoir d'où elle vient.

Table faits

Enfin, on rempli notre table faits f_prixCarburant qui contient les prix du carburant pour chaque station et chaque date
Le plus compliqué ici était d'avoir le prix de chaque carburant sur une ligne au lieu d'une ligne par carburant par station par date. Il fallait aussi faire attention aux dates, c'est pour ça qu'on a vérifié que les prix avaient une date avant d'importer les prix. On retire aussi les tirets des dates pour correspondre aux types date du logiciel.

Finalisation de l'entrepôt

Création de la table de synthèse

On a mis en place une table de synthèse pour avoir directement à chaque fois toutes les données qui nous intéressent dans une table, qui se mettra à jour à chaque fois. On trie les dates par ordre décroissant pour avoir en priorités les prix qui sont connus, celles qui ont été mis à jour le plus récemment, quand ça commence en 2020 on a beaucoup plus de valeurs manquantes.

Workflow d'initialisation

Le workflow d'initialisation permet d'avoir les données qu'on posède dans l'entrepôt quand on le lance. On commence par effacer les tables si elles existent puis les recréer pour qu'elles soient vierges et qu'on soit sûrs de pouvoir insérer les données. Ensuite, on active les trois pipelines qu'on a créées précedemment, l'ordre est important : si les tables station et date sont vides , on aura une erreur quand on voudra remplir la table des prix. Enfin, on reprends le script sql qui a permis de créer la table de synthèse en rajoutant la condition de l'effacer si elle existe déjà comme pour les autres tables, puis la table de synthèse est alimentée.

Données finales de la table de synthèse

Enfin, on obtient la table des prix avec la date qui était souhaitée. On est capable de faire des calculs sur les prix au mois, on voit qu'on a beaucoup de valeurs qui manquent pour les carburants autre que le gasole, pour certaines stations on a le prix que d'un ou deux carburant.